package manager;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * Servlet implementation class SrvConsulta
 */
@WebServlet("/SrvConsulta")
public class SrvConsulta extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public SrvConsulta() {
        super();
        // TODO Auto-generated constructor stub
    }
    
    protected void ProcesarPeticion(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
    	response.setContentType("text/html;charset=ISO-8859-1");
		PrintWriter out = response.getWriter();
		db conexion =  null;
		
		String operacion = (String)request.getParameter("operacion");
		
		if (operacion.equals("consulta")) { // Listado de PDA's
			try {
				conexion = new db();
				String sql = "select proydesc,marcdesc,serial,estadesc,catedesc,ubicaciones.nombre,activo,locadesc,depadesc " +
						"from equipos,marcas,proyectos,ubicaciones,categorias,estados,departamentos,localidad " +
						"where marca=marccodi " +
						"and proyecto=proycodi " +
						"and categoria=catecodi " +
						"and estado=estacodi " +
						"and locadepa = depacodi " +
						"and ciudad = locacodi " +
						"and ubicacion=ubicaciones.codigo " ;
						
						if (request.getParameter("proyecto") != null) {
							sql += "and proyecto = '" + (String)request.getParameter("proyecto") + "'";
						}
						
						if (request.getParameter("estado") != null) {
							sql += " and estado = '" + (String)request.getParameter("estado") + "'";
						}
						if (request.getParameter("marca") != null) {
							sql += " and marca = '" + (String)request.getParameter("marca") + "'";
						}
						
						if (request.getParameter("ubicacion") != null) {
							sql += " and ubicacion = '" + (String)request.getParameter("ubicacion") + "'";
						}
						
												
						if (request.getParameter("categoria") != null) {
							sql += " and categoria = '" + (String)request.getParameter("categoria") + "'";
						}
						
						if (request.getParameter("departamento") != null) {
							sql += " and depacodi = '" + (String)request.getParameter("departamento") + "'";
						}
						if (request.getParameter("ciudad") != null) {
							sql += " and ciudad = '" + (String)request.getParameter("ciudad") + "'";
						}
				
						sql += "order by proydesc,marcdesc,catedesc,serial";
				
				ResultSet rs = conexion.Query(sql);
				
					out.println("<h2>Listado Equipos</h2>");
					out.println("<table>");
					out.println("<tr>");
					out.println("<th>Proyecto</th>");
					out.println("<th>Marca</th>");
					out.println("<th>Serial</th>");
					out.println("<th>Estado</th>");
					out.println("<th>Categoria</th>");
					out.println("<th>Ubicacion</th>");
					out.println("<th>Dpto/Ciudad</th>");
					out.println("<th>Activo</th>");
					out.println("</tr>");
					if (rs.next()) { // existen departamentos
					do {
						out.println("<tr>");
						out.println("<td>" + rs.getString("proydesc") + "</td>");
						out.println("<td>" + rs.getString("marcdesc") + "</td>");
						out.println("<td><a href=\"javascript:Informacion('" + rs.getString("serial") + "')\">" + rs.getString("serial")  +"</a></td>");
						out.println("<td>" + rs.getString("estadesc") + "</td>");
						out.println("<td>" + rs.getString("catedesc") + "</td>");
						out.println("<td>" + rs.getString("nombre") + "</td>");
						out.println("<td>" + rs.getString("depadesc") + " - " + rs.getString("locadesc") + "</td>");
						out.println("<td>"
								+ (rs.getInt("activo") == 1 ? "Si" : "No")
								+ "</td>");
						out.println("</tr>");

					} while (rs.next());
					out.println("</table>");
				}else {
					
					out.println("<td colspan=\"7\">No se encontraron registros con el criterio de busqueda<td>");
				}

			} catch (SQLException e) {
				// TODO Auto-generated catch block
				out.print(e.getMessage());
			} finally {
				if (conexion != null) {
					try {
						conexion.Close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						out.print(e.getMessage());
					}
				}
			}

		}
		
		
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		this.ProcesarPeticion(request, response);
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		this.ProcesarPeticion(request, response);
	}

}
